package DAO;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Course;
import model.CourseSubject;
import model.Subject;
import util.DataUtil;

public class CousreSubjectDAO {

    public static List<CourseSubject> getAll() {
        List<CourseSubject> list = new ArrayList<CourseSubject>();
        String call = "{Call GET_ALL_COURSE_SUBJECT}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                CourseSubject s = new CourseSubject();
                Course course = new Course();
                course.setCourseID(rs.getInt("CourseID"));
                course.setCourseName(rs.getString("CourseName"));
                Subject sbj = new Subject();
                sbj.setSubjectId(rs.getString("SubjectID"));
                sbj.setSubjectName(rs.getString("SubName"));
                s.setCourse(course);
                s.setSubject(sbj);
                s.setYear(rs.getInt("Year"));
                s.setHour(rs.getInt("HourPerWeek"));
                list.add(s);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static List<CourseSubject> getAll(Course crs) {
        List<CourseSubject> list = new ArrayList<CourseSubject>();
        String call = "select * from Subject_Course inner join Course on Subject_Course.CourseID = Course.CourseID inner join Subject on Subject.SubjectID = Subject_Course.SubjectID where Course.CourseID = ?";
        try {
            PreparedStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setInt(1, crs.getCourseID());
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                CourseSubject s = new CourseSubject();
                Course course = new Course();
                course.setCourseID(rs.getInt("CourseID"));
                course.setCourseName(rs.getString("CourseName"));
                Subject sbj = new Subject();
                sbj.setSubjectId(rs.getString("SubjectID"));
                sbj.setSubjectName(rs.getString("SubName"));
                s.setCourse(course);
                s.setSubject(sbj);
                s.setYear(rs.getInt("Year"));
                s.setHour(rs.getInt("HourPerWeek"));
                list.add(s);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean addCourseSubject(CourseSubject cs) {
        int rs = 0;
        String call = "{Call ADD_COURSE_SUBJECT(?,?,?,?)}";
        try {
            CallableStatement css = DataUtil.getConnection().prepareCall(call);
            css.setInt(1, cs.getCourse().getCourseID());
            css.setString(2, cs.getSubject().getSubjectId());
            css.setInt(3, cs.getYear());
            css.setInt(4, cs.getHour());
            rs = css.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean deleteCourseSubject(int courseID, String subjectID) {
        int rs = 0;
        String call = "{Call DELETE_COURSE_SUBJECT(?,?)}";
        try {
            CallableStatement css = DataUtil.getConnection().prepareCall(call);
            css.setInt(1, courseID);
            css.setString(2, subjectID);
            rs = css.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean editCourseSubject(CourseSubject cs) {
        int rs = 0;
        String call = "{Call EDIT_COURSE_SUBJECT(?,?,?,?)}";
        try {
            CallableStatement css = DataUtil.getConnection().prepareCall(call);
            css.setInt(1, cs.getCourse().getCourseID());
            css.setString(2, cs.getSubject().getSubjectId());
            css.setInt(3, cs.getYear());
            css.setInt(4, cs.getHour());
            rs = css.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }
    
    public static List<CourseSubject> search(String filter,String key){
        List<CourseSubject> list = new ArrayList<CourseSubject>();
        for(CourseSubject sc: getAll()){
            if(filter.equals("Course ID")){
                if((sc.getCourse().getCourseID()+"").equals(key.toLowerCase())){
                    list.add(sc);
                }
            }
            if(filter.equals("Course Name")){
                if((sc.getCourse().getCourseName().toLowerCase()).equals(key.toLowerCase())){
                    list.add(sc);
                }
            }
            if(filter.equals("Subject ID")){
                if((sc.getSubject().getSubjectId().toLowerCase()).equals(key.toLowerCase())){
                    list.add(sc);
                }
            }
            if(filter.equals("Subject Name")){
                if((sc.getSubject().getSubjectName().toLowerCase()).equals(key.toLowerCase())){
                    list.add(sc);
                }
            }
            if(filter.equals("Hour Per Week")){
                if(((sc.getHour()+"").toLowerCase()).equals(key.toLowerCase())){
                    list.add(sc);
                }
            }
            if(filter.equals("Year")){
                if(((sc.getYear()+"").toLowerCase()).equals(key.toLowerCase())){
                    list.add(sc);
                }
            }
            
        }
        return list;
    }
}
